﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.IDAL;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class MasEducationInstituteDAL : IMasEducationInstituteDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public MasEducationInstituteDAL()
        {

        }
        public DataSet LoadEducationInstitutes(MasEducationInstituteInfo insInfo)
        {
            string strSQL;
            int CountrySeq = 0;
            if (insInfo.Countries != null)
            {
                CountrySeq = (int)insInfo.Countries.Sequence;
                if (CountrySeq.ToString() != SystemInfo._Local)
                    CountrySeq = 1;
            }

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Seq", insInfo.Sequence);
                dbManager.AddParameters(2, "@CountrySeq", CountrySeq);
                dbManager.AddParameters(3, "@Filter", insInfo.strFilter);

                strSQL = "SELECT ins.EDU_INS_SEQ, ins.EDU_INS_NAME, ins.EDU_INS_ENG, ins.EDU_GOV"
                        + ", ins.MAX_EDU_DEGREE_SEQ, ins.ADDRESS, ins.POSTAL_CODE, ins.TEL, ins.FAX"
                        + ", ins.DISTRICT_SEQ, ins.COUNTRY_SEQ, ins.EDU_INS_CODE"
                        + ", ins.RECORD_STATUS, ins.CREATED_BY, ins.UPDATED_BY"
                        + ", d.DISTRICT_NAME, c.COUNTRY_NAME"
                        + ", CASE WHEN ins.EDU_GOV = 1 THEN CAST('รัฐบาล' AS CHAR)"
                            + " ELSE CAST('เอกชน' AS CHAR)"
                            + " END AS EDU_GOV_NAME"
                        + ", CONCAT(ins.ADDRESS, ' ', d.DISTRICT_NAME, ' ', a.AMP_NAME, ' ', p.PROV_NAME, ' ', ins.POSTAL_CODE)"
                            + " AS ADDRESS_FULL"
                        + ", CONCAT('ตำบล', d.DISTRICT_NAME, ' อำเภอ', a.AMP_NAME, ' จังหวัด', p.PROV_NAME)"
                            + " AS DIS_AMP_PROV"
                    + " FROM MAS_EDU_INSTITUTES ins"
                        + " LEFT JOIN MAS_DISTRICTS d ON ins.DISTRICT_SEQ = d.DISTRICT_SEQ"
                        + " LEFT JOIN MAS_AMPHURS a ON d.AMP_SEQ = a.AMP_SEQ"
                        + " LEFT JOIN MAS_PROVINCES p ON a.PROV_SEQ = p.PROV_SEQ"
                        + " LEFT JOIN MAS_COUNTRIES c ON ins.COUNTRY_SEQ = c.COUNTRY_SEQ"
                    + " WHERE ins.RECORD_STATUS = @ActiveRecord"
                    + (insInfo.Sequence > 0 ? " AND ins.EDU_INS_SEQ = @Seq" : "")
                    + (CountrySeq > 0 ? CountrySeq == 1 ? " AND ins.COUNTRY_SEQ not like @CountrySeq" : " AND ins.COUNTRY_SEQ = @CountrySeq" : "")
                    //+ " AND CASE WHEN @Filter = '' THEN 0 = 0"
                    //    + " ELSE (ins.EDU_INS_NAME LIKE CONCAT('%', @Filter , '%')"
                    //        + " OR c.COUNTRY_NAME LIKE CONCAT('%', @Filter , '%')"
                    //    + ") END"
                    + ( String.IsNullOrEmpty(insInfo.strFilter) ? "" : " AND (ins.EDU_INS_NAME LIKE CONCAT('%', @Filter , '%')"
                            + " OR c.COUNTRY_NAME LIKE CONCAT('%', @Filter , '%') )")
                    + " ORDER BY ins.EDU_INS_CODE";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);                
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public IList<MasEducationInstituteInfo> ListEducationInstitutes(MasEducationInstituteInfo insInfo)
        {
            string strSQL;
            int CountrySeq = 0;
            if (insInfo.Countries != null)
            {
                CountrySeq = (int)insInfo.Countries.Sequence;
                if (CountrySeq.ToString() != SystemInfo._Local)
                    CountrySeq = 1;
            }

            List<MasEducationInstituteInfo> infoList = new List<MasEducationInstituteInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Seq", insInfo.Sequence);
                dbManager.AddParameters(2, "@CountrySeq", CountrySeq);

                strSQL = "SELECT EDU_INS_SEQ, EDU_INS_NAME, EDU_INS_ENG, EDU_GOV, MAX_EDU_DEGREE_SEQ"
                    + ", ADDRESS, POSTAL_CODE, TEL, FAX, DISTRICT_SEQ, COUNTRY_SEQ"
                    + ", EDU_INS_CODE, RECORD_STATUS, CREATED_BY, UPDATED_BY"
                    + " FROM MAS_EDU_INSTITUTES "
                    + " WHERE RECORD_STATUS not like @DeleteRecord"
                    + (insInfo.Sequence > 0 ? " AND EDU_INS_SEQ = @Seq" : "")
                    + (CountrySeq > 0 ? CountrySeq == 1 ? " AND COUNTRY_SEQ not like @CountrySeq" : " AND COUNTRY_SEQ = @CountrySeq" : "")
                    + " ORDER BY EDU_INS_CODE";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    //********* District **********************************
                    MasDistrictDAL disDAL = new MasDistrictDAL();
                    MasDistrictInfo disInfo = new MasDistrictInfo();
                    if (!rdr.IsDBNull(9))
                    {
                        IList<MasDistrictInfo> disList = disDAL.ListDistricts(new MasDistrictInfo(rdr.IsDBNull(9) ? 0 : rdr.GetInt32(9)));
                        disInfo = disList[0];
                    }
                    else
                        disInfo = null;

                    //********* Country ***********************************
                    MasCountryDAL countryDAL = new MasCountryDAL();
                    MasCountryInfo ctrInfo = new MasCountryInfo();
                    if (!rdr.IsDBNull(10))
                    {
                        IList<MasCountryInfo> countryList = countryDAL.ListMasCountries(new MasCountryInfo((rdr.IsDBNull(10)) ? 0 : rdr.GetInt32(10)));
                        if (countryList.Count > 0)
                            ctrInfo = countryList[0];
                    }

                    MasEducationDegreeDAL degreeDAL = new MasEducationDegreeDAL();
                    MasEducationDegreeInfo degreeInfo = new MasEducationDegreeInfo();
                    if (!rdr.IsDBNull(4))
                    {
                        IList<MasEducationDegreeInfo> degreeList = degreeDAL.ListEducationDegree(new MasEducationDegreeInfo(rdr.IsDBNull(4) ? 0 : rdr.GetInt32(4)));
                        if (degreeList.Count > 0)
                            degreeInfo = degreeList[0];
                    }
                    else
                        degreeInfo = null;
                    
                    MasEducationInstituteInfo Info = new MasEducationInstituteInfo(rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0)
                            , rdr.IsDBNull(11) ? "" : rdr.GetString(11)
                            , rdr.IsDBNull(1) ? "" : rdr.GetString(1)
                            , rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                            , rdr.IsDBNull(3) ? 0 : rdr.GetInt32(3)
                            , new AddressInfo(rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                                , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                                , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                                , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                                , "", "")
                            , disInfo
                            , ctrInfo
                            , degreeInfo
                            , rdr.IsDBNull(12) ? "" : rdr.GetString(12)
                            , rdr.IsDBNull(13) ? (int?)null : rdr.GetInt32(13)
                            , rdr.IsDBNull(14) ? (int?)null : rdr.GetInt32(14));
                    infoList.Add(Info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }        

        public void Insert(MasEducationInstituteInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@Code", Info.Code);
                dbManager.AddParameters(1, "@edu_ins_name", Info.Name);
                dbManager.AddParameters(2, "@edu_ins_eng", Info.NameEng);
                dbManager.AddParameters(3, "@edu_gov", Info.Governments);
                dbManager.AddParameters(4, "@address", Info.Address.Address);
                dbManager.AddParameters(5, "@district_seq", Info.Districts.Sequence > 0 ? Info.Districts.Sequence : null);//Info.Address.Districts.Sequence);
                dbManager.AddParameters(6, "@postal_code", Info.Address.PostalCode);
                dbManager.AddParameters(7, "@tel", Info.Address.Phone);
                dbManager.AddParameters(8, "@fax", Info.Address.Fax);
                dbManager.AddParameters(9, "@country_seq", Info.Countries.Sequence);
                dbManager.AddParameters(10, "@max_edu_degree_seq", Info.EducationDegree.Sequence);
                dbManager.AddParameters(11, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(12, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(13, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO MAS_EDU_INSTITUTES"
                    + "(EDU_INS_CODE, EDU_INS_NAME, EDU_INS_ENG, EDU_GOV, ADDRESS, DISTRICT_SEQ, POSTAL_CODE, TEL, FAX"
                        + ", COUNTRY_SEQ, MAX_EDU_DEGREE_SEQ, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@Code, @edu_ins_name, @edu_ins_eng, @edu_gov, @address, @district_seq, @postal_code, @tel"
                        + ", @fax, @country_seq, @max_edu_degree_seq, @RecordStatus, @CreatedBy, @CreatedWhen)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(MasEducationInstituteInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@Code", Info.Code);
                dbManager.AddParameters(2, "@edu_ins_name", Info.Name);
                dbManager.AddParameters(3, "@edu_ins_eng", Info.NameEng);
                dbManager.AddParameters(4, "@edu_gov", Info.Governments);
                dbManager.AddParameters(5, "@address", Info.Address.Address);
                dbManager.AddParameters(6, "@district_seq", Info.Districts.Sequence > 0 ? Info.Districts.Sequence : null);//Info.Address.Districts.Sequence);
                dbManager.AddParameters(7, "@postal_code", Info.Address.PostalCode);
                dbManager.AddParameters(8, "@tel", Info.Address.Phone);
                dbManager.AddParameters(9, "@fax", Info.Address.Fax);
                dbManager.AddParameters(10, "@country_seq", Info.Countries.Sequence);
                dbManager.AddParameters(11, "@max_edu_degree_seq", Info.EducationDegree.Sequence);
                dbManager.AddParameters(12, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(14, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE MAS_EDU_INSTITUTES"
                        + " SET EDU_INS_CODE = @Code"
                        + ", EDU_INS_NAME = @edu_ins_name"
                        + ", EDU_INS_ENG = @edu_ins_eng"
                        + ", EDU_GOV = @edu_gov"
                        + ", ADDRESS = @address"
                        + ", DISTRICT_SEQ = @district_seq"
                        + ", POSTAL_CODE = @postal_code"
                        + ", TEL = @tel"
                        + ", FAX = @fax"
                        + ", COUNTRY_SEQ = @country_seq"
                        + ", MAX_EDU_DEGREE_SEQ = @max_edu_degree_seq"
                        //+ ", RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE EDU_INS_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE MAS_EDU_INSTITUTES"
                        + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE EDU_INS_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
